﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.IO.Ports;
using System.Data;

namespace Pasteles.DataBase
{
    class Connection
    {
        string sConfig_hostname;
        string sConfig_dbname;
        string sConfig_dbusername;
        string sConfig_dbpassword;

        string sConfig_SerialPort;
        string sConfig_Baudrate;
        string sConfig_Parity;
        string sConfig_Datalength;

        string sConfig_line1 = "";
        string sConfig_line2 = "";
        string sConfig_line3 = "";
        string sConfig_line4 = "";
        string sConfig_line5 = "";

        MySqlConnection mySQLConnection;
        private string p;
        public Connection(string configFile)
        {
            readConfigurationFile(configFile);
            mySQLConnection = new MySqlConnection();
            mySQLConnection= preparingMySQL();
        }
        public Connection()
        {
 
        }

        private void readConfigurationFile(string configFile)
        {
            XmlReader reader = XmlReader.Create(configFile);
            while (reader.Read())
            {
                if (reader.NodeType == XmlNodeType.Element
                    && reader.Name == "Pasteles")
                {
                    sConfig_hostname = reader.GetAttribute(0);
                    sConfig_dbname = reader.GetAttribute(1);
                    sConfig_dbusername = reader.GetAttribute(2);
                    sConfig_dbpassword = reader.GetAttribute(3);

                    while (reader.NodeType != XmlNodeType.EndElement)
                    {
                        reader.Read();
                        if (reader.Name == "Printing")
                        {
                            /*MessageBox.Show("SerialPort= " + reader.GetAttribute(0));
                            MessageBox.Show("Baudrate= " + reader.GetAttribute(1));
                            MessageBox.Show("Parity= " + reader.GetAttribute(2));
                            MessageBox.Show("Datalength= " + reader.GetAttribute(3));
                            MessageBox.Show("Stopbits= " + reader.GetAttribute(4));*/
                            sConfig_SerialPort = reader.GetAttribute(0);
                            sConfig_Baudrate = reader.GetAttribute(1);
                            sConfig_Parity = reader.GetAttribute(2);
                            sConfig_Datalength = reader.GetAttribute(3);
                            //sConfig_Stopbits=reader.GetAttribute(4);

                            while (reader.NodeType != XmlNodeType.EndElement)
                            {
                                reader.Read();
                                if (reader.Name == "line1")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line1 = " + reader.Value);
                                            sConfig_line1 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if

                                if (reader.Name == "line2")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line2 = " + reader.Value);
                                            sConfig_line2 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                                if (reader.Name == "line3")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line3 = " + reader.Value);
                                            sConfig_line3 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                                if (reader.Name == "line4")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line4 = " + reader.Value);
                                            sConfig_line4 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                                if (reader.Name == "line5")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line5 = " + reader.Value);
                                            sConfig_line5 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                            }
                        } //end if
                    } //end while
                } //end if
            } //end while
            reader.Close();
        }
        public MySqlConnection preparingMySQL()
        {

            string MyConString = "SERVER=" + sConfig_hostname + ";" +
                "DATABASE=" + sConfig_dbname + ";" +
                "UID=" + sConfig_dbusername + ";" +
                "PASSWORD=" + sConfig_dbpassword + ";Allow zero DateTime=true;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            //MySqlCommand command = connection.CreateCommand();
            //MySqlDataReader Reader;
            //connection
            try
            {
                connection.Open();
                // MessageBox.Show("Connect to MySQL OK!");

            }
            catch (Exception error)
            {
                MessageBox.Show("Connection Error " + error.ToString());
            }
            connection.Close();
            return connection;
        }
        public SerialPort preparingSerialPort()
        {
            SerialPort _serialPort=new SerialPort();
            try
            {
                _serialPort = new SerialPort(sConfig_SerialPort,
                                            System.Convert.ToInt32(sConfig_Baudrate),
                                            Parity.None, System.Convert.ToInt32(sConfig_Datalength),
                                            StopBits.One);
                /*
                try
                {
                   // _serialPort.Handshake = Handshake.RequestToSendXOnXOff;
                    _serialPort.Handshake = Handshake.RequestToSend;
                    sConfig_SafePrinting = SettingsForm.sConfig_SafePrinting;
                    logToFile("sConfig_SafePrinting: " + sConfig_SafePrinting);
                }
                catch (Exception ex) {
                    logToFile("Serialport Handshake error"); 
                }*/
                //  _serialPort.DataReceived += new SerialDataReceivedEventHandler(sp_DataReceived);
                _serialPort.ReadTimeout = 500;
                _serialPort.WriteTimeout = 500;
                _serialPort.Open();
                //return _serialPort;
            }
            catch (Exception error)
            {
                MessageBox.Show("Serial port Error " + error.ToString());
            }
            return _serialPort;
            
        }
        public string preparingBillHeader()
        {
            return "       " + sConfig_line1 + "\r\n" + "     " + sConfig_line2 + "\r\n" +
                                        "      "+sConfig_line3 + "\r\n" +
                                        "     "+sConfig_line5 + "\r\n";
        }
        public DataTable excecuteQuery(string sQuery,MySqlConnection connection)
        {
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, connection);
                myDA.SelectCommand.ExecuteNonQuery();
                myDA.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                connection.Close();
            }
            return dt;
        }
        public DataTable excecuteQueryToDataTable(string sQuery)
        {
            DataTable dt = new DataTable();
            try
            {

                mySQLConnection.Open();
                MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, mySQLConnection);
                myDA.SelectCommand.ExecuteNonQuery();
                myDA.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mySQLConnection.Close();
            }
            return dt;
        }
        public void excecuteQuery(string sQuery)
        {
            //DataTable dt = new DataTable();
            try
            {
                mySQLConnection.Open();
                MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, mySQLConnection);
                myDA.SelectCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mySQLConnection.Close();
            }
            //return dt;
        }
        
    }
}
